#!bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d '-1 day' +%F`
fi

ADS_SEARCH_DAILY_SUMMARY_D_SQL="
INSERT OVERWRITE TABLE car_home_warehouse.ads_search_daily_summary_d
SELECT
    '${data_date}' AS stat_date,
    COUNT(*) AS total_search_count,
    COUNT(user_id) AS total_user_count,
    COUNT( device_id) AS total_device_count,
    COUNT( CASE WHEN user_id IS NOT NULL THEN user_id END) AS login_user_count,
    COUNT(session_id) AS total_session_count,
    AVG(CASE WHEN user_id IS NOT NULL THEN 1 ELSE 0 END) * COUNT(*) / COUNT( user_id) AS avg_search_per_user
FROM
    car_home_warehouse.dwd_auto_home_search_log
where dt='${data_date}'
GROUP BY
    user_id,session_id,device_id
;
"

ADS_SEARCH_HOT_KEYWORD_TOP20_D_SQL="
INSERT OVERWRITE TABLE car_home_warehouse.ads_search_hot_keyword_top20_d
SELECT
    '${data_date}' AS stat_date,
    search_keyword AS keyword,
    RANK() OVER (ORDER BY COUNT(*) DESC) AS search_rank,
    COUNT(*) AS search_count,
    COUNT( user_id) AS user_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS search_percentage,
    brand_id,
    series_id,
    model_id,
    price_range
FROM
    car_home_warehouse.dwd_auto_home_search_log
where dt='${data_date}'
GROUP BY
    search_keyword,
    brand_id,
    series_id,
    model_id,
    price_range,
    user_id
ORDER BY
    search_rank
    LIMIT 20;
"

ADS_USER_SEARCH_DEPTH_D_SQL="
INSERT OVERWRITE TABLE car_home_warehouse.ads_user_search_depth_d
SELECT
    '${data_date}' AS stat_date, -- 统计日期
    CASE
        WHEN search_count = 1 THEN '1次'
        WHEN search_count BETWEEN 2 AND 5 THEN '2-5次'
        WHEN search_count BETWEEN 6 AND 10 THEN '6-10次'
        WHEN search_count BETWEEN 11 AND 20 THEN '11-20次'
        ELSE '20次以上'
        END AS search_depth_range,
    COUNT( user_id) AS user_count,
    ROUND(COUNT( user_id) * 100.0 / SUM(COUNT( user_id)) OVER (), 2) AS percentage
FROM (
         SELECT

             user_id,
             COUNT(*) AS search_count
         FROM
             car_home_warehouse.dwd_auto_home_search_log
         where dt='${data_date}'
         GROUP BY

             user_id
     ) t
GROUP BY
    search_depth_range,user_id;
"

ADS_USER_SEARCH_TIME_DIST_D_SQL="
INSERT OVERWRITE TABLE car_home_warehouse.ads_user_search_time_dist_d
SELECT
        ${data_date} AS stat_date,
    CASE
        WHEN hour >= 0 AND hour < 6 THEN '00:00-06:00'
        WHEN hour >= 6 AND hour < 9 THEN '06:00-09:00'
        WHEN hour >= 9 AND hour < 12 THEN '09:00-12:00'
        WHEN hour >= 12 AND hour < 14 THEN '12:00-14:00'
        WHEN hour >= 14 AND hour < 18 THEN '14:00-18:00'
        WHEN hour >= 18 AND hour < 22 THEN '18:00-22:00'
        ELSE '22:00-24:00'
        END AS hour_range,
    COUNT(*) AS search_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM (
         SELECT
             HOUR(from_unixtime(CAST(search_time AS BIGINT))) AS hour
         FROM
             car_home_warehouse.dwd_auto_home_search_log
         where dt='${data_date}'
     ) t
GROUP BY
    hour_range
;
"

ADS_BRAND_SEARCH_RANK_D_SQL="
INSERT OVERWRITE TABLE car_home_warehouse.ads_brand_search_rank_d
SELECT
    '${data_date}' AS stat_date,
    brand_id,
    COUNT(*) AS search_count,
    COUNT( user_id) AS user_count,
    RANK() OVER (ORDER BY COUNT(*) DESC) AS rank,
        ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS search_percentage,
    ROUND(COUNT( user_id) * 100.0 / SUM(COUNT( user_id)) OVER (), 2) AS user_percentage
FROM
    car_home_warehouse.dwd_auto_home_search_log
where dt='${data_date}'
GROUP BY
    brand_id,user_id;
"

ADS_PRICE_RANGE_SEARCH_DIST_D_SQL="
INSERT OVERWRITE TABLE car_home_warehouse.ads_price_range_search_dist_d
SELECT
    '${data_date}' AS stat_date,
    price_range,
    COUNT(*) AS search_count,
    COUNT( user_id) AS user_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS search_percentage,
    ROUND(COUNT( user_id) * 100.0 / SUM(COUNT( user_id)) OVER (), 2) AS user_percentage
FROM
    car_home_warehouse.dwd_auto_home_search_log
where dt='${data_date}'
GROUP BY
    price_range,user_id
;
"

ADS_SEARCH_WEEK_OVER_WEEK_D_SQL="
INSERT OVERWRITE TABLE car_home_warehouse.ads_search_week_over_week_d
SELECT
    c.stat_date,
    c.current_week_count,
    last_week.last_week_count,
    CASE
        WHEN last_week.last_week_count = 0 THEN NULL
        ELSE (c.current_week_count - last_week.last_week_count) / last_week.last_week_count
        END AS wow_growth_rate
FROM (
         SELECT
             '${data_date}' AS stat_date,
             COUNT(*) AS current_week_count
         FROM
             car_home_warehouse.dwd_auto_home_search_log
         WHERE
             dt BETWEEN DATE_SUB('${data_date}', 7) AND '${data_date}'

     ) c
LEFT JOIN (
    SELECT
        '${data_date}' AS stat_date,
        COUNT(*) AS last_week_count
    FROM
        car_home_warehouse.dwd_auto_home_search_log
    WHERE
        dt BETWEEN DATE_SUB('${data_date}', 14) AND DATE_SUB('${data_date}', 7)

) last_week
ON  c.stat_date = DATE_ADD(last_week.stat_date, 7)
ORDER BY
    c.stat_date;
"

ADS_HOT_BRAND_TREND_7D_SQL="
INSERT OVERWRITE TABLE car_home_warehouse.ads_hot_brand_trend_7d
SELECT
    brand_id,
    '${data_date}',
    COUNT(*) AS search_count,
    COUNT( user_id) AS user_count,
    RANK() OVER (ORDER BY COUNT(*) DESC) AS daily_rank
FROM
    car_home_warehouse.dwd_auto_home_search_log
WHERE
    dt BETWEEN DATE_SUB('${data_date}', 7) AND '${data_date}'
    AND brand_id IS NOT NULL
GROUP BY
    brand_id,
    user_id
;
"

ADS_PROVINCE_SEARCH_RANK_D_SQL="
INSERT OVERWRITE TABLE car_home_warehouse.ads_province_search_rank_d
SELECT
    '${data_date}',
    province,
    COUNT(*) AS search_count,
    COUNT( user_id) AS user_count,
    RANK() OVER (ORDER BY COUNT(*) DESC) AS rank,
        ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS search_percentage,
    ROUND(COUNT( user_id) * 100.0 / SUM(COUNT( user_id)) OVER (), 2) AS user_percentage
FROM
    car_home_warehouse.dwd_auto_home_search_log
where dt= '${data_date}'
GROUP BY
    province,
    user_id
;
"

ADS_CITY_SEARCH_RANK_D_SQL="
INSERT OVERWRITE TABLE car_home_warehouse.ads_city_search_rank_d
SELECT
'${data_date}' as stat_date,
    province,
    city,
    COUNT(*) AS search_count, 
    COUNT( user_id) AS user_count, 
    RANK() OVER (PARTITION BY province ORDER BY COUNT(*) DESC) AS rank,
        ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY province), 2) AS search_percentage, 
    ROUND(COUNT( user_id) * 100.0 / SUM(COUNT( user_id)) OVER (PARTITION BY province), 2) AS user_percentage 
FROM
    car_home_warehouse.dwd_auto_home_search_log
where dt= '${data_date}'
GROUP BY
    province,
    city,
    user_id
;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -p 123456 -e "${ADS_SEARCH_DAILY_SUMMARY_D_SQL}${ADS_SEARCH_HOT_KEYWORD_TOP20_D_SQL}${ADS_USER_SEARCH_DEPTH_D_SQL}${ADS_USER_SEARCH_TIME_DIST_D_SQL}${ADS_BRAND_SEARCH_RANK_D_SQL}${ADS_PRICE_RANGE_SEARCH_DIST_D_SQL}${ADS_SEARCH_WEEK_OVER_WEEK_D_SQL}${ADS_HOT_BRAND_TREND_7D_SQL}${ADS_PROVINCE_SEARCH_RANK_D_SQL}${ADS_CITY_SEARCH_RANK_D_SQL}"

